package dao

import (
	"fmt"
	"strings"
	"time"
	"yundian/internel/app/helper"
	"yundian/internel/app/helper/request"
	"yundian/internel/app/models/statistics"
	"yundian/internel/pkg/dingo"
	"yundian/internel/pkg/dingo/global"
)

// GetPlaceInfoScanNum 门店分析-门店详情数据
func (d *StatisticsDao) GetPlaceInfoScanNum(placeId int) *dingo.ErrorWrap {
	dbInfo := &request.GetAnalysisPlaceInfoDataRes{}

	fields := `SUM(IF(sl.id>0,1,0))scan_num,sl.place_id`
	db := global.Orm.Table("scan_logs sl").Select(fields)
	db.Where("place_id=?", placeId)

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetPlaceInfoDeviceData 门店分析-门店详情数据
func (d *StatisticsDao) GetPlaceInfoDeviceData(placeId int) *dingo.ErrorWrap {
	dbInfo := &request.GetAnalysisPlaceInfoDataRes{}

	fields := `SUM(IF(prod_type=1,1,0))device_num_line,SUM(IF(prod_type=2,1,0))
	device_num_water,SUM(IF(prod_type=3,1,0))device_num_automate,d.place_id,p.place_name`
	db := global.Orm.Table("devices d").Select(fields)
	db.Joins("LEFT JOIN places p ON d.place_id=p.id")
	db.Where("place_id=?", placeId)

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetPlaceInfoOrderDataList 门店分析-门店详情数据
func (d *StatisticsDao) GetPlaceInfoOrderDataList(placeIds []int, month string) *dingo.ErrorWrap {
	var dbList []*request.GetPlaceInfoOrderDataRes

	if month == "" {
		sql := `SELECT 1 type,place_id, SUM(IFNULL(amount,0))settlement_total,COUNT(1)order_num FROM automate_orders ao WHERE 
		place_id IN ? AND status=5 GROUP BY place_id UNION SELECT 2 type,place_id,SUM(IFNULL(amount,0))settlement_total,COUNT(1)
		order_num FROM room_orders ao WHERE place_id IN ? AND status=5 GROUP BY place_id UNION SELECT 3 type,place_id,SUM(IFNULL
		(amount,0))settlement_total,COUNT(1)order_num FROM goods_orders ao WHERE place_id IN ? AND status=4 GROUP BY place_id`

		db := global.Orm.Raw(sql, placeIds, placeIds, placeIds).Find(&dbList)
		return dingo.ErrorIf(db.Error, dbList)
	}

	whereList := []interface{}{placeIds, month, placeIds, month, placeIds, month}
	sql := `SELECT 1 type,place_id, SUM(IFNULL(amount,0))settlement_total,COUNT(1)order_num FROM automate_orders ao WHERE 
	place_id IN ? AND status=5 AND DATE_FORMAT(create_at,'%Y-%m')=? GROUP BY place_id UNION SELECT 2 type,place_id,SUM(
	IFNULL(amount,0))settlement_total,COUNT(1)order_num FROM room_orders ao WHERE place_id IN ? AND status=5 AND DATE_FORMAT
	(create_at,'%Y-%m')=? GROUP BY place_id UNION SELECT 3 type,place_id,SUM(IFNULL(amount,0))settlement_total,COUNT(1)
	order_num FROM goods_orders ao WHERE place_id IN ? AND status=4 AND DATE_FORMAT(create_at,'%Y-%m')=? GROUP BY place_id`

	db := global.Orm.Raw(sql, whereList...).Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetPlaceOrderList 查询门店订单数据
func (d *StatisticsDao) GetPlaceOrderList(placeIds []int, month string) *dingo.ErrorWrap {
	var dbList []*request.GetAnalysisPlaceListRes

	where := ``
	whereList := []interface{}{placeIds}
	if month != "" {
		where += "AND DATE_FORMAT(FROM_UNIXTIME(end_at),'%Y-%m')=?"
		whereList = append(whereList, month)
	}

	sql := fmt.Sprintf(`SELECT SUM(settlement_money)settlement_total,SUM(order_num)order_num,COUNT(1)use_device_num,
	place_id FROM (SELECT SUM(o.settlement_money)settlement_money,COUNT(1)order_num, place_id FROM orders o WHERE place_id 
	IN ? AND state=5 AND is_draw=0 %s GROUP BY o.device_sn,o.place_id)o1 GROUP BY o1.place_id`, where)
	db := global.Orm.Raw(sql, whereList...).Find(&dbList)

	return dingo.ErrorIf(db.Error, dbList)
}

// GetPlaceDeviceList 查询门店设备总数
func (d *StatisticsDao) GetPlaceDeviceList(placeIds []int, month string) *dingo.ErrorWrap {
	var dbList []*request.GetAnalysisPlaceListRes

	if len(placeIds) == 0 {
		return dingo.ErrorIf(nil, dbList)
	}

	if month == "" {
		fields := `COUNT(1)device_num,d.place_id`
		db := global.Orm.Table("devices d").Select(fields)
		db.Where("place_id IN ?", placeIds)
		db.Group("d.place_id").Find(&dbList)
		return dingo.ErrorIf(db.Error, dbList)
	}

	sql := `SELECT pi.place_id,SUM(IF(pi.income_day=pi1.income_day, pi.device_total, 0))device_num FROM place_income pi
	LEFT JOIN (SELECT MAX(income_day)income_day,place_id FROM place_income WHERE place_id IN ? AND LEFT(income_day,7)=? 
	GROUP BY place_id)pi1 ON pi.place_id=pi1.place_id AND pi.income_day=pi1.income_day
	WHERE pi.place_id IN ? AND LEFT(pi.income_day,7)=? GROUP BY pi.place_id`
	db := global.Orm.Raw(sql, placeIds, month, placeIds, month).Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetPlaceList 门店分析-门店列表
func (d *StatisticsDao) GetPlaceList(param *request.GetAnalysisPlaceListReq) *dingo.ErrorWrap {
	var (
		dbList []*request.GetAnalysisPlaceListRes
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)

	fields := `SUM(IF(sl.id>0,1,0))scan_num,p.place_name,p.id place_id,
	DATE_FORMAT(FROM_UNIXTIME(p.create_at),'%Y-%m-%d %T')in_time`
	db := global.Orm.Table("places p").Select(fields)

	if param.Month == "" {
		db.Joins("LEFT JOIN scan_logs sl ON p.id=sl.place_id")
	} else {
		db.Joins("LEFT JOIN scan_logs sl ON p.id=sl.place_id AND DATE_FORMAT(FROM_UNIXTIME(sl.create_at),'%Y-%m')=?", param.Month)
	}

	if !helper.IsAdminAndStaff(param.IsAdmin, param.ParentId) {
		userIdArr := NewUserDao().QueryChildUserIds(param.UserId).Unwrap().([]int)
		placesIdArr := NewUserDao().GetPlacesIds(param.UserId, userIdArr).Unwrap().([]int)
		db.Where("p.id IN ?", placesIdArr)
	}

	if param.PlaceName != "" || param.Phone != "" {
		db.Joins("LEFT JOIN users u ON u.id=p.user_id")
		db.Where("u.phone LIKE ? OR u.user_name LIKE ?", "%"+param.Phone+"%", "%"+param.UserName+"%")
	}

	if param.PlaceName != "" {
		db.Where("p.place_name LIKE ?", "%"+param.PlaceName+"%")
	}

	if param.Sort == 1 {
		db.Order("scan_num DESC")
	}
	if param.Sort == 2 {
		db.Order("scan_num")
	}

	count = d.GetPlaceListCount(param).Unwrap().(int64)
	db.Limit(limit).Offset(offset).Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList, count)
}

// GetPlaceListCount 门店分析-门店列表
func (d *StatisticsDao) GetPlaceListCount(param *request.GetAnalysisPlaceListReq) *dingo.ErrorWrap {
	var count int64

	db := global.Orm.Table("places p")
	if !helper.IsAdminAndStaff(param.IsAdmin, param.ParentId) {
		userIdArr := NewUserDao().QueryChildUserIds(param.UserId).Unwrap().([]int)
		placesIdArr := NewUserDao().GetPlacesIds(param.UserId, userIdArr).Unwrap().([]int)
		db.Where("p.id IN ?", placesIdArr)
	}

	if param.PlaceName != "" || param.Phone != "" {
		db.Joins("LEFT JOIN users u ON u.id=p.user_id")
		db.Where("u.phone LIKE ? OR u.user_name LIKE ?", "%"+param.Phone+"%", "%"+param.UserName+"%")
	}

	if param.PlaceName != "" {
		db.Where("p.place_name LIKE ?", "%"+param.PlaceName+"%")
	}

	db.Count(&count)
	return dingo.ErrorIf(db.Error, count)
}

// GetRoomIncomeMoneyList 查询续住收益-统计列表
func (d *StatisticsDao) GetRoomIncomeMoneyList(param *request.GetRoomDataListReq) *dingo.ErrorWrap {
	var dbList []*statistics.GetRoomIncomeMoneyListRes

	fields := `SUM(ro.amount)total_income,DATE_FORMAT(ro.create_time,'%Y-%m-%d')date`
	db := global.Orm.Table("room_orders ro").Select(fields)
	db.Where("ro.status=? AND ro.user_id=? AND ro.third_trade_no<>''", helper.TypeNumRoomOrderStatusDone, param.UserId)

	if param.Month != "" {
		db.Where("DATE_FORMAT(ro.create_time,'%Y-%m')=?", param.Month)
	}

	db.Group("date").Order("date").Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetRoomOrderList 查询续住订单数据
func (d *StatisticsDao) GetRoomOrderList(param *request.GetRoomDataListReq) *dingo.ErrorWrap {
	var dbList []*statistics.GetRoomOrderListRes

	fields := `SUM(ro.amount)settlement_total,COUNT(1)total_orders,IF(ro.type_id=null,0,rt.id)type_id,
	IF(rt.type_name='其它',0,rt.type_name)type_name,DATE_FORMAT(ro.create_time,'%Y-%m-%d')date`
	db := global.Orm.Table("room_orders ro").Select(fields)
	db.Joins("LEFT JOIN room_type rt ON ro.type_id=rt.id")
	db.Where("ro.status=? AND ro.user_id=? AND ro.third_trade_no<>''", helper.TypeNumRoomOrderStatusDone, param.UserId)

	if param.Month != "" {
		db.Where("DATE_FORMAT(ro.create_time,'%Y-%m')=?", param.Month)
	}

	db.Group("rt.id,date").Order("date").Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetRoomOrderData 查询续住订单数据
func (d *StatisticsDao) GetRoomOrderData(param *request.GetRoomDataListReq) *dingo.ErrorWrap {
	var dbInfo = &statistics.GetRoomOrderDataRes{}

	fields := `SUM(ro.amount)settlement_total,COUNT(1)total_orders,IF(ro.type_id=null,0,rt.id)
	type_id,IF(rt.type_name='其它',0,rt.type_name)type_name`
	db := global.Orm.Table("room_orders ro").Select(fields)
	db.Joins("LEFT JOIN room_type rt ON ro.type_id=rt.id")
	db.Where("ro.status=? AND ro.user_id=? AND ro.third_trade_no<>''", helper.TypeNumRoomOrderStatusDone, param.UserId)

	if param.Month != "" {
		db.Where("DATE_FORMAT(ro.create_time,'%Y-%m')=?", param.Month)
	}

	db.Group("rt.id").Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetRoomData 查询续住数据
func (d *StatisticsDao) GetRoomData(param *request.GetRoomDataListReq) *dingo.ErrorWrap {
	var dbInfo = &statistics.GetRoomDataRes{}

	fields := `SUM(ro.amount)settlement_total,COUNT(1)total_orders,
	ro.type_id,rt.type_name,DATE_FORMAT(NOW(),'%Y-%m-%d %T')date`
	db := global.Orm.Table("room_orders ro").Select(fields)
	db.Joins("LEFT JOIN room_type rt ON ro.type_id=rt.id")
	db.Where("ro.status=? AND ro.user_id=?", helper.TypeNumRoomOrderStatusDone, param.UserId)

	db.Find(&dbInfo)
	dbInfo.TotalIncome = dbInfo.SettlementTotal
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetGoodsIncomeMoneyList 查询客房超市收益-统计列表
func (d *StatisticsDao) GetGoodsIncomeMoneyList(param *request.GetGoodsDataListReq) *dingo.ErrorWrap {
	var dbList []*statistics.GetGoodsIncomeMoneyListRes

	fields := `DATE_FORMAT(go.create_time,'%Y-%m-%d')date,SUM(go.amount)total_income`
	db := global.Orm.Table("goods_orders go").Select(fields)
	db.Where("third_trade_no<>'' AND user_id=? AND status=?", param.UserId, helper.TypeNumGoodsOrderStatusShipped)

	if param.Month != "" {
		db.Where("DATE_FORMAT(go.create_time,'%Y-%m')=?", param.Month)
	}

	db.Group("date").Order("date")

	db.Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetGoodsOrderList 查询客房超市商品订单-统计列表
func (d *StatisticsDao) GetGoodsOrderList(param *request.GetGoodsDataListReq) *dingo.ErrorWrap {
	var dbList []*statistics.GetGoodsOrderListRes

	where := ``
	whereList := []interface{}{param.UserId, helper.TypeNumGoodsOrderStatusShipped}
	if param.Month != "" {
		where = `AND DATE_FORMAT(go.create_time,'%Y-%m')=?`
		whereList = append(whereList, param.Month)
	}

	sql := fmt.Sprintf(`SELECT COUNT(1)total_orders,SUM(total_goods)total_goods,SUM(SettlementTotal)SettlementTotal,
	SUM(SettlementTotal)total_income,DATE_FORMAT(create_time,'%s') date FROM (SELECT SUM(buy_num)total_goods,SUM(amount)
	SettlementTotal,go.create_time FROM goods_orders go WHERE third_trade_no<>'' AND user_id=? AND 
	status=? %s GROUP BY go.trade_no)a GROUP BY DATE_FORMAT(create_time,'%s') 
	ORDER BY create_time`, `%Y-%m-%d`, where, "%Y-%m-%d")
	db := global.Orm.Raw(sql, whereList...)

	db.Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetGoodsData 查询客房超市数据
func (d *StatisticsDao) GetGoodsData(param *request.GetGoodsDataListReq) *dingo.ErrorWrap {
	var dbInfo = &statistics.GetGoodsDataRes{}

	sql := `SELECT COUNT(1)total_orders,SUM(total_goods)total_goods,SUM(SettlementTotal)SettlementTotal,DATE_FORMAT(NOW(),
	'%Y-%m-%d %T') date FROM (SELECT SUM(buy_num)total_goods,SUM(amount)SettlementTotal FROM goods_orders go WHERE 
	third_trade_no<>'' AND user_id=? AND status=? GROUP BY go.trade_no)a`
	db := global.Orm.Raw(sql, param.UserId, helper.TypeNumGoodsOrderStatusShipped)

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetAutomateIncomeMoneyList 查询售货收益统计列表
func (d *StatisticsDao) GetAutomateIncomeMoneyList(param *request.GetAutomateDataListReq) *dingo.ErrorWrap {
	var dbList []*statistics.GetAutomateIncomeMoneyListRes

	fields := `SUM(adl.amount)total_income,DATE_FORMAT(adl.end_date,'%Y-%m-%d')date`
	db := global.Orm.Table("automate_dividends_log adl").Select(fields)
	db.Where("adl.user_id=?", param.UserId)

	if param.Month != "" {
		db.Where("adl.end_month=?", param.Month)
	}

	db.Group("adl.end_date").Order("adl.end_date").Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetAutomateOrderGoodsList 查询售货商品订单统计列表
func (d *StatisticsDao) GetAutomateOrderGoodsList(param *request.GetAutomateDataListReq) *dingo.ErrorWrap {
	var dbList []*statistics.GetAutomateOrderGoodsListRes

	fields := `SUM(ao.amount)settlement_total,COUNT(1)total_orders,SUM(adl.amount)total_income,
	DATE_FORMAT(aour.end_date,'%Y-%m-%d')date,ao.goods_name,ao.automate_good_id`
	db := global.Orm.Table("automate_orders_user_relevancy aour").Select(fields)
	db.Joins("LEFT JOIN automate_orders ao ON aour.trade_no=ao.trade_no")
	db.Joins("LEFT JOIN automate_dividends_log adl ON aour.trade_no=adl.trade_no AND aour.user_id=adl.user_id")
	db.Where("ao.status=? AND aour.user_id=?", helper.TypeNumOrderAutomateStatusWaitDone, param.UserId)

	if param.Month != "" {
		db.Where("aour.end_month=?", param.Month)
	}

	db.Group("aour.end_date,ao.automate_good_id").Order("ao.automate_good_id,aour.end_date").Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetAutomateOrderGoodsData 查询售货商品订单数据
func (d *StatisticsDao) GetAutomateOrderGoodsData(param *request.GetAutomateDataListReq) *dingo.ErrorWrap {
	var dbList []*statistics.GetAutomateOrderGoodsDataRes

	fields := `SUM(ao.amount)settlement_total,COUNT(1)total_orders,ao.goods_name,ao.automate_good_id`
	db := global.Orm.Table("automate_orders_user_relevancy aour").Select(fields)
	db.Joins("LEFT JOIN automate_orders ao ON aour.trade_no=ao.trade_no")
	db.Where("ao.status=? AND aour.user_id=?", helper.TypeNumOrderAutomateStatusWaitDone, param.UserId)

	if param.Month != "" {
		db.Where("aour.end_month=?", param.Month)
	}

	db.Group("ao.automate_good_id").Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetAutomateDevicesNumByUserId 查询用户下级设备数
func (d *StatisticsDao) GetAutomateDevicesNumByUserId(param *request.GetAutomateDataListReq) *dingo.ErrorWrap {
	var dbInfo = &statistics.GetAutomateDataRes{}

	where := ``
	var whereList []interface{}
	if !helper.IsAdminAndStaff(param.IsAdmin, param.ParentId) {
		where = `AND agb.place_id IN ?`
		userIdArr := NewUserDao().QueryChildUserIds(param.UserId).Unwrap().([]int)
		placesIdArr := NewUserDao().GetPlacesIds(param.UserId, userIdArr).Unwrap().([]int)
		whereList = append(whereList, placesIdArr)
	}

	sql := fmt.Sprintf(`SELECT COUNT(1)total_devices FROM (SELECT agb.device_sn FROM automate_goods_bind agb 
	LEFT JOIN automate_goods ag ON agb.automate_goods_id=ag.id WHERE ag.id>0 %s GROUP BY agb.device_sn)a`, where)
	db := global.Orm.Raw(sql, whereList...)

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetAutomateUserAnalysisAllData 查询用户总数据
func (d *StatisticsDao) GetAutomateUserAnalysisAllData(userId int) *dingo.ErrorWrap {
	var dbInfo = &statistics.GetAutomateDataRes{}

	sql := `SELECT SUM(ao.amount)settlement_total,SUM(adl.amount)total_income,COUNT(1)total_orders,
	DATE_FORMAT(NOW(),'%Y-%m-%d %T') date FROM automate_orders_user_relevancy aour LEFT JOIN 
	automate_dividends_log adl ON aour.trade_no=adl.trade_no LEFT JOIN automate_orders ao ON 
	aour.trade_no=ao.trade_no WHERE aour.user_id=? AND ao.status=?`
	db := global.Orm.Raw(sql, userId, helper.TypeNumOrderAutomateStatusWaitDone)

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetUserAnalysisUsePlaceDataDayList 查询用户动销门店数列表
func (d *StatisticsDao) GetUserAnalysisUsePlaceDataDayList(param *request.GetChargeDataListReq) *dingo.ErrorWrap {
	var dbList []*statistics.GetChargeDataListRes

	whereStr := `our.user_id=?`
	whereList := []interface{}{param.UserId}
	if param.Month != "" {
		whereStr += ` AND our.end_month=?`
		whereList = append(whereList, param.Month)
	}

	sql := fmt.Sprintf(`SELECT COUNT(1)num,DATE_FORMAT(end_date, '%s') date FROM (SELECT o.place_id,our.end_date 
	FROM orders_user_relevancy our LEFT JOIN orders o ON our.trade_no=o.trade_no WHERE %s GROUP BY 
	o.place_id,our.end_date)a GROUP BY end_date`, `%Y-%m-%d`, whereStr)
	db := global.Orm.Raw(sql, whereList...)

	db.Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetUserAnalysisUsePlaceData 查询用户动销门店数
func (d *StatisticsDao) GetUserAnalysisUsePlaceData(param *request.GetChargeDataListReq) *dingo.ErrorWrap {
	var dbInfo = &statistics.GetChargeDataListRes{}

	sql := `SELECT COUNT(1)num FROM (SELECT o.place_id FROM orders_user_relevancy our LEFT JOIN orders o ON 
	our.trade_no=o.trade_no WHERE our.user_id=? AND our.end_month=? GROUP BY o.place_id)a`
	db := global.Orm.Raw(sql, param.UserId, param.Month)

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetUserAnalysisLastDataByMonth 查询用户月份最新数据
func (d *StatisticsDao) GetUserAnalysisLastDataByMonth(param *request.GetChargeDataListReq) *dingo.ErrorWrap {
	var dbInfo = &statistics.GetGetChargeDataRes{}

	fields := `total_place,total_devices`
	db := global.Orm.Table("statistics_user su").Select(fields)
	db.Where("su.user_id=?", param.UserId)

	if param.Month != "" {
		db.Where("LEFT(date_str,7)=?", param.Month)
	}

	db.Order("date_str DESC").Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetUserAnalysisUseDeviceData 查询用户动销设备数
func (d *StatisticsDao) GetUserAnalysisUseDeviceData(param *request.GetChargeDataListReq) *dingo.ErrorWrap {
	var dbInfo = &statistics.GetChargeDataListRes{}

	whereStr := `our.user_id=?`
	whereList := []interface{}{param.UserId}
	if param.Month != "" {
		whereStr += ` AND our.end_month=?`
		whereList = append(whereList, param.Month)
	}

	sql := fmt.Sprintf(`SELECT COUNT(1)num FROM (SELECT o.device_sn FROM orders_user_relevancy our 
	LEFT JOIN orders o ON our.trade_no=o.trade_no WHERE %s GROUP BY o.device_sn)a`, whereStr)
	db := global.Orm.Raw(sql, whereList...)

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetUserAnalysisUseDeviceDataDayList 查询用户动销设备数列表
func (d *StatisticsDao) GetUserAnalysisUseDeviceDataDayList(param *request.GetChargeDataListReq) *dingo.ErrorWrap {
	var dbList []*statistics.GetChargeDataListRes

	whereStr := `our.user_id=?`
	whereList := []interface{}{param.UserId}
	if param.Month != "" {
		whereStr += ` AND our.end_month=?`
		whereList = append(whereList, param.Month)
	}

	sql := fmt.Sprintf(`SELECT COUNT(1)num,DATE_FORMAT(end_date, '%s') date FROM (SELECT o.device_sn,our.end_date 
	FROM orders_user_relevancy our LEFT JOIN orders o ON our.trade_no=o.trade_no WHERE %s GROUP BY 
	o.device_sn,our.end_date)a GROUP BY end_date`, `%Y-%m-%d`, whereStr)
	db := global.Orm.Raw(sql, whereList...)

	db.Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetUserAnalysisDataList 查询用户日期数据列表
func (d *StatisticsDao) GetUserAnalysisDataList(param *request.GetChargeDataListReq) *dingo.ErrorWrap {
	var dbList []*statistics.GetChargeDataListRes

	db := global.Orm.Table("statistics_user su").Select(param.Fields)
	db.Where("su.user_id=?", param.UserId)

	if param.Month != "" {
		db.Where("LEFT(date_str,7)=?", param.Month)
	}

	db.Order("date_str").Find(&dbList)
	return dingo.ErrorIf(db.Error, dbList)
}

// GetUserAnalysisLastData 查询用户最新数据
func (d *StatisticsDao) GetUserAnalysisLastData(userId int) *dingo.ErrorWrap {
	var dbInfo = &statistics.GetGetChargeDataRes{}

	fields := `total_place,total_devices,date_str date`
	db := global.Orm.Table("statistics_user su").Select(fields)
	db.Order("date_str DESC").Where("su.user_id=?", userId)

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// GetUserAnalysisAllData 查询用户总数据
func (d *StatisticsDao) GetUserAnalysisAllData(userId int) *dingo.ErrorWrap {
	var dbInfo = &statistics.GetGetChargeDataRes{}

	fields := `SUM(total_orders)total_orders,SUM(settlement_total)settlement_total,
	SUM(total_income)total_income,SUM(total_cancel)total_cancel`
	db := global.Orm.Table("statistics_user su").Select(fields)
	db.Where("su.user_id=?", userId)

	db.Find(&dbInfo)
	return dingo.ErrorIf(db.Error, dbInfo)
}

// 获取用户分析列表
func (d *StatisticsDao) GetUserAnalysisList(param *request.UserAnalysisListReq) *dingo.ErrorWrap {

	var (
		lst    []*statistics.StatisticsUserList
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)

	db := global.Orm.Debug().Table("statistics_user su").
		Select("su.*", "u.name", "u.phone", "u.user_no", "u.user_name", "u.is_admin", "u.province", "u.city").
		Joins("LEFT JOIN users u ON su.user_id = u.id")
	if param.Phone != "" {
		db.Where("u.phone = ?", param.Phone)
	} // 手机号码
	if param.Name != "" {
		db.Where("u.name = ?", param.Name)
	} // 姓名
	if param.UserName != "" {
		db.Where("u.user_name LIKE ?", fmt.Sprint("%", param.UserName, "%"))
	} // 名称
	if param.Address != "" { // 区域
		db.Where("u.province or u.city LIKE ?", fmt.Sprint("%", param.Address, "%"))
	}
	if param.BeginTime != "" { // 日期
		dateRange := strings.Split(param.BeginTime, ",")
		db.Where("su.date_str >= ?", dateRange[0])
		db.Where("su.date_str <= ?", dateRange[1])
	}
	if param.IsAdmin > 0 {
		db.Where("u.is_admin = ?", param.IsAdmin)
	}

	if param.IsSteward == 1 {
		db.Where("u.is_steward = ?", param.IsSteward)
	} else {
		db.Where("u.is_steward != ?", 1)
	}

	db.Order("su.date_str DESC")

	db.Limit(limit).Offset(offset).Find(&lst).Limit(-1).Offset(-1).Count(&count)

	return dingo.ErrorIf(db.Error, lst, count)
}

// GetUserRankList 获取用户数据分析排行(流水、收益)
func (d *StatisticsDao) GetUserSettlementAndIncomeRankList(param *request.UserAnalysisListReq, orderStr string) *dingo.ErrorWrap {
	var (
		lst    []*statistics.StatisticsUserRankList
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)
	db := global.Orm.Table("statistics_user su").
		Select("SUM(su.settlement_total) as settlement_total,SUM(su.settlement_draw) as settlement_draw," +
			"SUM(su.settlement_smalldraw) as settlement_smalldraw,SUM(su.settlement_nodraw) as settlement_nodraw," +
			"SUM(su.total_income) as total_income," +
			"su.user_id, u.name, u.user_name, u.phone, u.province, u.city").
		Joins("LEFT JOIN users u ON su.user_id = u.id")

	db.Group("su.user_id")

	if param.IsAdmin > 0 {
		db.Where("u.is_admin = ?", param.IsAdmin)
	}

	if param.IsSteward == 1 {
		db.Where("u.is_steward = ?", param.IsSteward)
	} else {
		db.Where("u.is_steward != ?", 1)
	}

	if orderStr == "settlement" {
		db.Order("settlement_total DESC")
	}
	if orderStr == "income" {
		db.Order("total_income DESC")
	}
	if orderStr == "place" {
		db.Order("su.total_place DESC")
	}
	if orderStr == "team" {
		db.Order("su.total_team DESC")
	}

	db.Limit(limit).Offset(offset).Find(&lst).Limit(-1).Offset(-1).Count(&count)

	return dingo.ErrorIf(db.Error, lst, count)
}

// GetUserTeamAndPlaceRankList 获取用户数据分析排行(团除、门店)
func (d *StatisticsDao) GetUserTeamAndPlaceRankList(param *request.UserAnalysisListReq, dateStr, orderStr string) *dingo.ErrorWrap {
	var (
		lst    []*statistics.StatisticsUserRankList
		count  int64
		limit  int = param.PageSize
		offset int = param.PageSize * (param.PageNo - 1)
	)
	db := global.Orm.Table("statistics_user su").
		Select("su.total_place, su.total_direct_place,su.total_team, su.total_direct_team, su.user_id, u.name, u.user_name, u.phone, u.province, u.city").
		Joins("LEFT JOIN users u ON su.user_id = u.id")

	db.Where("su.date_str = ?", dateStr)

	if param.IsAdmin > 0 {
		db.Where("u.is_admin = ?", param.IsAdmin)
	}

	if param.IsSteward == 1 {
		db.Where("u.is_steward = ?", param.IsSteward)
	} else {
		db.Where("u.is_steward != ?", 1)
	}

	if orderStr == "place" {
		db.Order("su.total_place DESC")
	}
	if orderStr == "team" {
		db.Order("su.total_team DESC")
	}

	db.Limit(limit).Offset(offset).Find(&lst).Limit(-1).Offset(-1).Count(&count)

	return dingo.ErrorIf(db.Error, lst, count)
}

// GetUserSettlementChart 获取用户流水折线图
func (d *StatisticsDao) GetUserSettlementChart(userId int, statisticsType string) *dingo.ErrorWrap {
	var list []statistics.UserSettlementChart

	db := global.Orm.Table("statistics_user")
	if statisticsType == "month" {
		db.Select("SUM(settlement_complete)/100 as settlement_complete, DATE_FORMAT(date_str,'%Y-%m') as date_str")
		db.Where("user_id = ?", userId)
		startDate := time.Now().AddDate(0, -10, 0).Format("2006-01")
		endDate := time.Now().AddDate(0, +1, 0).Format("2006-01") // 昨日
		db.Where("date_str >= ?", startDate)
		db.Where("date_str <= ?", endDate)
		db.Group("DATE_FORMAT(date_str,'%Y-%m')") // 按月排序
	} else {
		db.Select("settlement_total/100 as settlement_total, settlement_complete/100 as settlement_complete, date_str")
		// 获取最近 30 天日期
		startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
		endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02") // 昨日
		db.Where("date_str >= ?", startDate)
		db.Where("date_str <= ?", endDate)
		db.Where("user_id = ?", userId)
	}
	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// GetUserSettlementChart 获取用户收益折线图
func (d *StatisticsDao) GetUserIncomeChart(userId int, statisticsType string) *dingo.ErrorWrap {
	var list []statistics.IncomeChart

	db := global.Orm.Table("statistics_user")
	if statisticsType == "month" {
		db.Select("SUM(total_income)/100 as income_total, DATE_FORMAT(date_str,'%Y-%m') as date_str")
		db.Where("user_id = ?", userId)
		startDate := time.Now().AddDate(0, -10, 0).Format("2006-01")
		endDate := time.Now().AddDate(0, +1, 0).Format("2006-01") // 昨日
		db.Where("date_str >= ?", startDate)
		db.Where("date_str <= ?", endDate)
		db.Group("DATE_FORMAT(date_str,'%Y-%m')") // 按月排序
	} else {
		db.Select("total_income/100 as income_total, date_str")
		// 获取最近 30 天日期
		startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
		endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02") // 昨日
		db.Where("date_str >= ?", startDate)
		db.Where("date_str <= ?", endDate)
		db.Where("user_id = ?", userId)
	}

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// GetUserPriceChart 获取用户单价折线图
func (d *StatisticsDao) GetUserPriceChart(userId int, statisticsType string, isDraw uint8) *dingo.ErrorWrap {
	var list []statistics.PriceChart
	db := global.Orm.Table("statistics_user")

	if isDraw == 1 {
		if statisticsType == "month" {
			db.Select("DATE_FORMAT(date_str,'%Y-%m') as date_str, SUM(total_complete) as order_total, FORMAT(IFNULL((SUM(settlement_complete)/100)/SUM(total_complete),0),2) as price")
			db.Where("user_id = ?", userId)
			startDate := time.Now().AddDate(0, -10, 0).Format("2006-01")
			endDate := time.Now().AddDate(0, +1, 0).Format("2006-01")
			db.Where("date_str >= ?", startDate)
			db.Where("date_str <= ?", endDate)
			db.Group("DATE_FORMAT(date_str,'%Y-%m')") // 按月排序
		} else {
			db.Select("date_str, total_complete as order_total, FORMAT(IFNULL((settlement_complete/100)/total_complete,0),2) as price")
			// 获取最近 10 天日期
			startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
			endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")
			db.Where("date_str >= ?", startDate)
			db.Where("date_str <= ?", endDate)
			db.Where("user_id = ?", userId)
		}
	} else {
		if statisticsType == "month" {
			db.Select("DATE_FORMAT(date_str,'%Y-%m') as date_str, SUM(total_complete_nodraw) as order_total, FORMAT(IFNULL((SUM(settlement_normal_total)/100)/SUM(total_complete_nodraw),0),2) as price")
			db.Where("user_id = ?", userId)
			startDate := time.Now().AddDate(0, -10, 0).Format("2006-01")
			endDate := time.Now().AddDate(0, +1, 0).Format("2006-01")
			db.Where("date_str >= ?", startDate)
			db.Where("date_str <= ?", endDate)
			db.Group("DATE_FORMAT(date_str,'%Y-%m')") // 按月排序
		} else {
			db.Select("date_str, total_complete_nodraw as order_total, FORMAT(IFNULL((settlement_normal_total/100)/total_complete_nodraw,0),2) as price")
			// 获取最近 10 天日期
			startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
			endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")
			db.Where("date_str >= ?", startDate)
			db.Where("date_str <= ?", endDate)
			db.Where("user_id = ?", userId)
		}
	}

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// GetUserSaleChartByDay 门店动销
func (d *StatisticsDao) GetUserSaleChartByDay(userId int, statisticsType string) *dingo.ErrorWrap {
	var list []statistics.SaleChart
	db := global.Orm.Table("statistics_user")

	db.Select("date_str, total_place, total_devices, place_sale as count_place")
	startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")
	db.Where("date_str >= ?", startDate)
	db.Where("date_str <= ?", endDate)
	db.Where("user_id = ?", userId)

	//if statisticsType == "month" {
	//	db.Select("DATE_FORMAT(date_str,'%Y-%m') as date_str")
	//	db.Where("user_id = ?", userId)
	//	startDate := time.Now().AddDate(0, -10, 0).Format("2006-01")
	//	endDate := time.Now().AddDate(0, +1, 0).Format("2006-01")
	//	db.Where("date_str >= ?", startDate)
	//	db.Where("date_str <= ?", endDate)
	//	db.Group("DATE_FORMAT(date_str,'%Y-%m')") // 按月排序
	//} else {
	//	db.Select("date_str, total_place, total_devices, place_sale")
	//	startDate := time.Now().AddDate(0, 0, -30).Format("2006-01-02")
	//	endDate := time.Now().AddDate(0, 0, -1).Format("2006-01-02")
	//	db.Where("date_str >= ?", startDate)
	//	db.Where("date_str <= ?", endDate)
	//	db.Where("user_id = ?", userId)
	//}

	db.Find(&list)
	return dingo.ErrorIf(db.Error, list)
}

// GetUserAnalysisDetail 获取用户详情
func (d *StatisticsDao) GetUserAnalysisDetail(userId int, statisticsType string) *dingo.ErrorWrap {
	analysisInfo := &statistics.UserAnalysisInfo{}
	db := global.Orm.Table("statistics_user")

	//if statisticsType == "month" {
	//	db.Select("DATE_FORMAT(date_str,'%Y-%m') as date_str, SUM(settlement_complete)/100 as settlement_complete, SUM(total_income)/100 as total_income, SUM(total_complete) as total_complete")
	//	db.Where("user_id = ?", userId)
	//	db.Group("DATE_FORMAT(date_str,'%Y-%m')")
	//	db.Order("date_str DESC")
	//} else {
	//	db.Select("date_str, IFNULL(settlement_complete,0)/100 as settlement_complete, IFNULL(total_income,0)/100 as total_income, total_complete")
	//	db.Where("user_id = ?", userId)
	//	db.Group("date_str DESC")
	//}

	db.Select("DATE_FORMAT(date_str,'%Y-%m') as date_str, SUM(settlement_complete)/100 as settlement_complete, SUM(total_income)/100 as total_income, SUM(total_complete) as total_complete")
	db.Where("user_id = ?", userId)

	db.Find(&analysisInfo).Limit(1)
	return dingo.ErrorIf(db.Error, analysisInfo)
}

// 获取门店总数、设备总数
func (d *StatisticsDao) GetUserAnalysisPlaceTotal(userId int, dateStr string) *dingo.ErrorWrap {
	analysisInfo := &statistics.UserAnalysisInfo{}
	db := global.Orm.Table("statistics_user")
	db.Select("date_str, total_place, total_devices")
	db.Where("user_id = ?", userId)
	db.Where("date_str = ?", dateStr)

	db.Find(&analysisInfo).Limit(1)
	return dingo.ErrorIf(db.Error, analysisInfo)
}
